<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222"><meta name="generator" content="Hexo 6.3.0">

  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css" integrity="sha256-Z1K5uhUaJXA7Ll0XrZ/0JhX4lAtZFpT6jkKrEDT0drU=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"example.com","root":"/","images":"/images","scheme":"Muse","darkmode":false,"version":"8.14.1","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":false,"style":null},"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":-1,"unescape":false,"preload":false}}</script><script src="/js/config.js"></script>

    <meta name="description" content="12306究竟难在哪里需求分析12306 的业务数据量https:&#x2F;&#x2F;zhuanlan.zhihu.com&#x2F;p&#x2F;31074574 大数据时代互联网三高架构：高并发、高性能、高可用，简称三高（3H）互联网应用系统开发肯定经常会看到高并发和高性能这两个词，可谓是耳熟能详。对于12306这样的国民级应用来说，3H能否做到，直接关系到使用者的体验——也就是春运时能否安然买到火车票回家。 像12306这样如">
<meta property="og:type" content="article">
<meta property="og:title" content="12306究竟难在哪里">
<meta property="og:url" content="http://example.com/2021/09/07/12306/index.html">
<meta property="og:site_name" content="JsyBlog">
<meta property="og:description" content="12306究竟难在哪里需求分析12306 的业务数据量https:&#x2F;&#x2F;zhuanlan.zhihu.com&#x2F;p&#x2F;31074574 大数据时代互联网三高架构：高并发、高性能、高可用，简称三高（3H）互联网应用系统开发肯定经常会看到高并发和高性能这两个词，可谓是耳熟能详。对于12306这样的国民级应用来说，3H能否做到，直接关系到使用者的体验——也就是春运时能否安然买到火车票回家。 像12306这样如">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2021-09-06T16:00:00.000Z">
<meta property="article:modified_time" content="2022-03-15T20:13:32.250Z">
<meta property="article:author" content="SongyangJi">
<meta name="twitter:card" content="summary">


<link rel="canonical" href="http://example.com/2021/09/07/12306/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"http://example.com/2021/09/07/12306/","path":"2021/09/07/12306/","title":"12306究竟难在哪里"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>12306究竟难在哪里 | JsyBlog</title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title">JsyBlog</p>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#12306%E7%A9%B6%E7%AB%9F%E9%9A%BE%E5%9C%A8%E5%93%AA%E9%87%8C"><span class="nav-number">1.</span> <span class="nav-text">12306究竟难在哪里</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%9C%80%E6%B1%82%E5%88%86%E6%9E%90"><span class="nav-number">1.1.</span> <span class="nav-text">需求分析</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#12306-%E7%9A%84%E4%B8%9A%E5%8A%A1%E6%95%B0%E6%8D%AE%E9%87%8F"><span class="nav-number">1.1.1.</span> <span class="nav-text">12306 的业务数据量</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%A4%A7%E6%95%B0%E6%8D%AE%E6%97%B6%E4%BB%A3"><span class="nav-number">1.1.2.</span> <span class="nav-text">大数据时代</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%AB%98%E5%8F%AF%E7%94%A8"><span class="nav-number">1.1.3.</span> <span class="nav-text">高可用</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#SOA%EF%BC%88%E5%88%86%E5%B8%83%E5%BC%8F%E6%9C%8D%E5%8A%A1%E7%B3%BB%E7%BB%9F%EF%BC%89"><span class="nav-number">1.1.3.1.</span> <span class="nav-text">SOA（分布式服务系统）</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%AB%98%E5%B9%B6%E5%8F%91-%E4%B8%8E-%E9%AB%98%E6%80%A7%E8%83%BD"><span class="nav-number">1.1.4.</span> <span class="nav-text">高并发 与 高性能</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%AB%98%E6%95%88%E7%9A%84IO"><span class="nav-number">1.2.</span> <span class="nav-text">高效的IO</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%BC%93%E5%AD%98"><span class="nav-number">1.2.1.</span> <span class="nav-text">缓存</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#innodb"><span class="nav-number">1.2.1.1.</span> <span class="nav-text">innodb</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#mybatis-%E4%B8%80%E7%BA%A7%E7%BC%93%E5%AD%98%E3%80%81%E4%BA%8C%E7%BA%A7%E7%BC%93%E5%AD%98"><span class="nav-number">1.2.1.2.</span> <span class="nav-text">mybatis 一级缓存、二级缓存</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#%E4%B8%80%E7%BA%A7%E7%BC%93%E5%AD%98"><span class="nav-number">1.2.1.2.1.</span> <span class="nav-text">一级缓存</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#%E4%BA%8C%E7%BA%A7%E7%BC%93%E5%AD%98"><span class="nav-number">1.2.1.2.2.</span> <span class="nav-text">二级缓存</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#redis"><span class="nav-number">1.2.1.3.</span> <span class="nav-text">redis</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%B3%BB%E7%BB%9F%E6%9E%B6%E6%9E%84"><span class="nav-number">1.3.</span> <span class="nav-text">系统架构</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%88%86%E7%89%87"><span class="nav-number">1.3.1.</span> <span class="nav-text">数据分片</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E9%9B%86%E7%BE%A4"><span class="nav-number">1.3.2.</span> <span class="nav-text">数据库集群</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#%E4%B8%BB%E8%8A%82%E7%82%B9-log-dump-%E7%BA%BF%E7%A8%8B"><span class="nav-number">1.3.2.0.1.</span> <span class="nav-text">主节点 log dump 线程</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#%E4%BB%8E%E8%8A%82%E7%82%B9I-x2F-O%E7%BA%BF%E7%A8%8B"><span class="nav-number">1.3.2.0.2.</span> <span class="nav-text">从节点I&#x2F;O线程</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#relay-log"><span class="nav-number">1.3.2.0.3.</span> <span class="nav-text">relay log</span></a></li></ol></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1"><span class="nav-number">1.4.</span> <span class="nav-text">数据库设计</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%88%97%E8%BD%A6%E3%80%81%E7%AB%99%E7%82%B9%E4%B8%8E%E5%9B%BE%E8%AE%BA%E6%A8%A1%E5%9E%8B"><span class="nav-number">1.4.1.</span> <span class="nav-text">列车、站点与图论模型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#ER%E5%9B%BE"><span class="nav-number">1.4.2.</span> <span class="nav-text">ER图</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%9A%E5%8A%A1%E9%87%8F%E7%A9%B6%E7%AB%9F%E5%A4%A7%E5%9C%A8%E5%93%AA%E9%87%8C"><span class="nav-number">1.4.3.</span> <span class="nav-text">业务量究竟大在哪里</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%BA%93%E5%AD%98%E6%98%AF%E4%BB%80%E4%B9%88-%E2%80%94%E2%80%94-%E7%81%AB%E8%BD%A6%E5%94%AE%E7%A5%A8%E7%9A%84%E4%BD%99%E7%A5%A8%E7%AE%97%E6%B3%95"><span class="nav-number">1.4.4.</span> <span class="nav-text">库存是什么? —— 火车售票的余票算法</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%B7%AF%E5%BE%84%E8%8A%82%E7%82%B9%E5%AF%B9%E6%9E%84%E6%88%90-sku"><span class="nav-number">1.4.4.1.</span> <span class="nav-text">路径节点对构成 sku</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E7%AB%99%E7%82%B9%E5%BA%A7%E4%BD%8Dbitmap%E6%B3%95"><span class="nav-number">1.4.4.2.</span> <span class="nav-text">站点座位bitmap法</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E2%80%9D%E5%BA%93%E5%AD%98%E2%80%9C%E6%89%A3%E5%87%8F%E7%9A%84%E9%97%AE%E9%A2%98"><span class="nav-number">1.5.</span> <span class="nav-text">”库存“扣减的问题</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%B6%85%E5%8D%96%E9%97%AE%E9%A2%98%E7%9A%84%E8%A7%A3%E5%86%B3"><span class="nav-number">1.5.1.</span> <span class="nav-text">超卖问题的解决</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%82%B2%E8%A7%82%E9%94%81"><span class="nav-number">1.5.1.1.</span> <span class="nav-text">悲观锁</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%B9%90%E8%A7%82%E9%94%81"><span class="nav-number">1.5.1.2.</span> <span class="nav-text">乐观锁</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%87%AA%E9%80%82%E5%BA%94%E9%94%81"><span class="nav-number">1.5.1.3.</span> <span class="nav-text">自适应锁</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%8A%80%E6%9C%AF%E9%9A%BE%E7%82%B9%E4%B8%BE%E4%BE%8B"><span class="nav-number">1.6.</span> <span class="nav-text">技术难点举例</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%BA%93%E5%AD%98%E8%B6%85%E5%8D%96"><span class="nav-number">1.6.1.</span> <span class="nav-text">库存超卖</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%81%BF%E5%85%8D%E9%87%8D%E5%A4%8D%E4%B8%8B%E5%8D%95"><span class="nav-number">1.6.2.</span> <span class="nav-text">避免重复下单</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%A2%E5%8D%95%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%EF%BC%8C%E5%A4%9A%E7%BB%B4%E5%BA%A6%E6%9F%A5%E8%AF%A2"><span class="nav-number">1.6.3.</span> <span class="nav-text">订单分库分表，多维度查询</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1"><span class="nav-number">1.6.4.</span> <span class="nav-text">分布式事务</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#XA"><span class="nav-number">1.6.4.1.</span> <span class="nav-text">XA</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#Seata-%E7%9A%84-AT"><span class="nav-number">1.6.4.2.</span> <span class="nav-text">Seata 的 AT</span></a></li></ol></li></ol></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">SongyangJi</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">251</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">45</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">109</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>

        </div>
      </div>
    </div>

    
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://example.com/2021/09/07/12306/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="SongyangJi">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="JsyBlog">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="12306究竟难在哪里 | JsyBlog">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          12306究竟难在哪里
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2021-09-07 00:00:00" itemprop="dateCreated datePublished" datetime="2021-09-07T00:00:00+08:00">2021-09-07</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2022-03-16 04:13:32" itemprop="dateModified" datetime="2022-03-16T04:13:32+08:00">2022-03-16</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E6%9E%B6%E6%9E%84/" itemprop="url" rel="index"><span itemprop="name">架构</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h1 id="12306究竟难在哪里"><a href="#12306究竟难在哪里" class="headerlink" title="12306究竟难在哪里"></a>12306究竟难在哪里</h1><h2 id="需求分析"><a href="#需求分析" class="headerlink" title="需求分析"></a>需求分析</h2><h3 id="12306-的业务数据量"><a href="#12306-的业务数据量" class="headerlink" title="12306 的业务数据量"></a>12306 的业务数据量</h3><p><a target="_blank" rel="noopener" href="https://zhuanlan.zhihu.com/p/31074574">https://zhuanlan.zhihu.com/p/31074574</a></p>
<h3 id="大数据时代"><a href="#大数据时代" class="headerlink" title="大数据时代"></a>大数据时代</h3><p>互联网三高架构：高并发、高性能、高可用，简称三高（3H）<br>互联网应用系统开发肯定经常会看到高并发和高性能这两个词，可谓是耳熟能详。<br>对于12306这样的国民级应用来说，3H能否做到，直接关系到使用者的体验——也就是春运时能否安然买到火车票回家。</p>
<p>像12306这样如此大规模的分布式架构系统，深入研究其架构基础来说对于我们还为时尚早，<br>但是罗马不是一天建成的，此次数据课课设我也不妨从几个技术点，结合当前主流的工具去实践一番，探究其中数据库层面可能遇到的技术瓶颈, 从而窥一斑而见全豹，深化自身对数据库的理解，加强使用的熟练度。</p>
<h3 id="高可用"><a href="#高可用" class="headerlink" title="高可用"></a>高可用</h3><p>集群：一主多从（读写分离的配置）mysql binlog</p>
<p>分布式：数据分片  sharding、mycat</p>
<h4 id="SOA（分布式服务系统）"><a href="#SOA（分布式服务系统）" class="headerlink" title="SOA（分布式服务系统）"></a>SOA（分布式服务系统）</h4><p>面向服务的体系结构（英语：service-oriented architecture）并不特指一种技术，而是一种分布式运算的软件设计方法。软件的部分组件（调用者），可以透过网络上的通用协议调用另一个应用软件组件运行、运作，让调用者获得服务。SOA原则上采用开放标准、与软件资源进行交互并采用表示的标准方式。因此应能跨越厂商、产品与技术。一项服务应视为一个独立的功能单元，可以远程访问并独立运行与更新，例如在线查询信用卡账单。</p>
<p>图</p>
<h3 id="高并发-与-高性能"><a href="#高并发-与-高性能" class="headerlink" title="高并发 与 高性能"></a>高并发 与 高性能</h3><p>高效的IO、高效的计算、流量的负载均衡</p>
<h2 id="高效的IO"><a href="#高效的IO" class="headerlink" title="高效的IO"></a>高效的IO</h2><h3 id="缓存"><a href="#缓存" class="headerlink" title="缓存"></a>缓存</h3><p>读多写少的二八法则在计算机世界里到处都是，下面从三个层面的缓存去深化对缓存的使用。</p>
<h4 id="innodb"><a href="#innodb" class="headerlink" title="innodb"></a>innodb</h4><p>innodb-buffer-pool设置多大<br>Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。</p>
<p>innodb_buffer_pool_size 参数为innodb_buffer_pool的大小设置。<br>innodb_buffer_pool_chunk_size参数为InnoDB缓冲池块大小。<br>innodb_buffer_pool_instances参数为缓冲池实例的个数。</p>
<p>规则：<br>innodb_buffer_pool_size &#x3D; innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances *N</p>
<p>系统默认的innodb_buffer_pool_chunk_size为128M<br>innodb_buffer_pool_instances参数的默认设置为1 最大设置为64 ，但是将innodb_buffer_pool_size大小设置为1GB或更大时，此选项才生效。（主要是防止有太多小的instance从而导致性能问题。）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> @<span class="variable">@innodb</span>_buffer_pool_size<span class="operator">/</span><span class="number">1024</span><span class="operator">/</span><span class="number">1024</span><span class="operator">/</span><span class="number">1024</span>;</span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> innodb_buffer_pool_size <span class="operator">=</span> <span class="number">4227858432</span>;</span><br></pre></td></tr></table></figure>


<p>建议设置为系统内存的50%-80%，但也不是越大越好，要根据具体项目具体分析（操作系统留1G左右，mysql连接数*4M，宿主程序缓存nM）。</p>
<p>查看缓冲池状态</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> status <span class="keyword">like</span> <span class="string">&#x27;Innodb_buffer_pool_%&#x27;</span></span><br></pre></td></tr></table></figure>
<p>变量解析<br>Innodb_buffer_pool_pages_total参数表示缓存页面的总数量;<br>Innodb_buffer_pool_pages_data代表有数据的缓存页数;<br>Innodb_buffer_pool_pages_free代表没有使用的缓存页数;<br>Innodb_buffer_pool_pages_misc: innodb buffer pool缓存池中当前已经被用作管理用途或hash index而不能用作为普通数据页的数目。</p>
<p>Innodb_buffer_pool_read_requests表示read请求的次数，<br>Innodb_buffer_pool_reads表示从物理磁盘中读取数据的请求次数，</p>
<p>innodb buffer的read命中率 &#x3D;（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads） &#x2F; Innodb_buffer_pool_read_requests * 100%。<br>如果这个命中率小于95%，建议增大 innodb_buffer_pool_size</p>
<p>如果Innodb_buffer_pool_pages_free偏大的话，证明有很多缓存没有被利用到，这时可以考虑减小缓存;<br>相反Innodb_buffer_pool_pages_data过大就考虑增大缓存。</p>
<p>配置建议规则 （来自 阿里RDS配置参考）</p>
<table>
<thead>
<tr>
<th align="left">实例内存大小（单位：MB）</th>
<th align="left">默认Buffer Pool（单位：MB）</th>
<th align="left">推荐最大Buffer Pool（单位：MB）</th>
</tr>
</thead>
<tbody><tr>
<td align="left">1024</td>
<td align="left">256</td>
<td align="left">256</td>
</tr>
<tr>
<td align="left">2048</td>
<td align="left">512</td>
<td align="left">512</td>
</tr>
<tr>
<td align="left">4096</td>
<td align="left">1536</td>
<td align="left">1536</td>
</tr>
<tr>
<td align="left">8192</td>
<td align="left">4608</td>
<td align="left">4608</td>
</tr>
<tr>
<td align="left">16384</td>
<td align="left">12288</td>
<td align="left">12288</td>
</tr>
<tr>
<td align="left">24576</td>
<td align="left">18432</td>
<td align="left">19456</td>
</tr>
<tr>
<td align="left">32768</td>
<td align="left">24576</td>
<td align="left">25600</td>
</tr>
<tr>
<td align="left">49152</td>
<td align="left">36864</td>
<td align="left">38912</td>
</tr>
<tr>
<td align="left">65536</td>
<td align="left">49152</td>
<td align="left">52224</td>
</tr>
<tr>
<td align="left">98304</td>
<td align="left">73728</td>
<td align="left">77824</td>
</tr>
<tr>
<td align="left">131072</td>
<td align="left">98304</td>
<td align="left">104448</td>
</tr>
<tr>
<td align="left">196608</td>
<td align="left">147456</td>
<td align="left">156672</td>
</tr>
<tr>
<td align="left">229376</td>
<td align="left">172032</td>
<td align="left">183296</td>
</tr>
<tr>
<td align="left">262144</td>
<td align="left">196608</td>
<td align="left">208896</td>
</tr>
<tr>
<td align="left">393216</td>
<td align="left">294912</td>
<td align="left">314368</td>
</tr>
<tr>
<td align="left">491520</td>
<td align="left">368640</td>
<td align="left">393216</td>
</tr>
<tr>
<td align="left">786432</td>
<td align="left">589824</td>
<td align="left">628736</td>
</tr>
</tbody></table>
<h4 id="mybatis-一级缓存、二级缓存"><a href="#mybatis-一级缓存、二级缓存" class="headerlink" title="mybatis 一级缓存、二级缓存"></a>mybatis 一级缓存、二级缓存</h4><h5 id="一级缓存"><a href="#一级缓存" class="headerlink" title="一级缓存"></a>一级缓存</h5><p>查询时：只要两条SQL的下列五个值相同，即可以认为是相同的SQL。<br>Statement Id + Offset + Limmit + Sql + Params</p>
<p>更新时：每次执行update前都会清空localCache,避免脏读</p>
<ol>
<li>MyBatis一级缓存的生命周期和SqlSession一致，级缓存只在数据库会话内部共享。</li>
<li>MyBatis一级缓存内部设计简单，只是一个没有容量限定的HashMap，在缓存的功能性上有所欠缺。</li>
<li>MyBatis的一级缓存最大范围是SqlSession内部，有多个SqlSession或者分布式的环境下，数据库写操作会引起旧数据（不过考虑到mysql的MVCC下的RR，也是有价值的），建议设定缓存级别为Statement。</li>
</ol>
<h5 id="二级缓存"><a href="#二级缓存" class="headerlink" title="二级缓存"></a>二级缓存</h5><p>二级缓存 -&gt; 一级缓存 -&gt; 数据库。</p>
<p>当sqlsession没有调用commit()方法时，二级缓存并没有起到作用。<br>（但是在spring容器管理下的sqlsession 在没有开启事务的时，是不会commit的，二级缓存根本用不上）<br>即使是在 @Transactional 下，又有多少会在事务里调用同一段sql呢？</p>
<ol>
<li>MyBatis的二级缓存相对于一级缓存来说，实现了SqlSession之间缓存数据的共享，同时粒度更加的细，能够到namespace级别，通过Cache接口实现类不同的组合，对Cache的可控性也更强。</li>
<li>MyBatis在多表查询时，极大可能会出现脏数据，有设计上的缺陷，安全使用二级缓存的条件比较苛刻。</li>
<li>在分布式环境下，由于默认的MyBatis Cache实现都是基于本地的，分布式环境下必然会出现读取到脏数据，需要使用集中式缓存将MyBatis的Cache接口实现，有一定的开发成本，直接使用Redis、Memcached等分布式缓存可能成本更低，安全性也更高。</li>
</ol>
<h4 id="redis"><a href="#redis" class="headerlink" title="redis"></a>redis</h4><p>略去<br>更新逻辑</p>
<h2 id="系统架构"><a href="#系统架构" class="headerlink" title="系统架构"></a>系统架构</h2><h3 id="数据分片"><a href="#数据分片" class="headerlink" title="数据分片"></a>数据分片</h3><p>分库分表的需要</p>
<p>垂直分片 </p>
<p>水平分片</p>
<p>分库分表带来的问题：</p>
<ol>
<li>维护困难</li>
<li>sql的支持度</li>
<li>跨库事务</li>
<li>跨库Join<br>后面会结合项目的具体问题进行细讲</li>
</ol>
<h3 id="数据库集群"><a href="#数据库集群" class="headerlink" title="数据库集群"></a>数据库集群</h3><p>数据分片与主从复制</p>
<p>主库会生成一个 log dump 线程,用来给从库 I&#x2F;O 线程传 Binlog 数据。</p>
<p>从库的 I&#x2F;O 线程会去请求主库的 Binlog，并将得到的 Binlog 写到本地的 relay log (中继日志)文件中。</p>
<p>SQL 线程,会读取 relay log 文件中的日志，并解析成 SQL 语句逐一执行。</p>
<h5 id="主节点-log-dump-线程"><a href="#主节点-log-dump-线程" class="headerlink" title="主节点 log dump 线程"></a>主节点 log dump 线程</h5><p>当从节点连接主节点时，主节点会为其创建一个 log dump 线程，用于发送和读取 Binlog 的内容。在读取 Binlog 中的操作时，log dump 线程会对主节点上的 Binlog 加锁；当读取完成发送给从节点之前，锁会被释放。<strong>主节点会为自己的每一个从节点创建一个 log dump 线程</strong>。</p>
<h5 id="从节点I-x2F-O线程"><a href="#从节点I-x2F-O线程" class="headerlink" title="从节点I&#x2F;O线程"></a>从节点I&#x2F;O线程</h5><p>当从节点上执行<code>start slave</code>命令之后，从节点会创建一个 I&#x2F;O 线程用来连接主节点，请求主库中更新的Binlog。I&#x2F;O 线程接收到主节点的 log dump 进程发来的更新之后，保存在本地 relay-log（中继日志）中。</p>
<h5 id="relay-log"><a href="#relay-log" class="headerlink" title="relay log"></a>relay log</h5><p>这里又引申出一个新的日志概念。MySQL 进行主主复制或主从复制的时候会在要复制的服务器下面产生相应的 relay log。</p>
<p>relay log 是怎么产生的呢？</p>
<p>从服务器 I&#x2F;O 线程将主服务器的 Binlog 日志读取过来，解析到各类 Events 之后记录到从服务器本地文件，这个文件就被称为 relay log。然后 SQL 线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。中继日志充当缓冲区，这样 master 就不必等待 slave 执行完成才发送下一个事件。</p>
<h2 id="数据库设计"><a href="#数据库设计" class="headerlink" title="数据库设计"></a>数据库设计</h2><h3 id="列车、站点与图论模型"><a href="#列车、站点与图论模型" class="headerlink" title="列车、站点与图论模型"></a>列车、站点与图论模型</h3><p>列车抽象为图论中的路径<br>车站抽象为图论中的点</p>
<p>不过，上述的建模还不够全面，这仅仅是对静态结构的描述，还要加上时间这个维度。</p>
<p>在本次系统实现中，使用Quartz的CronTrigger（基于日历的调度，可精确到秒），进行列车状态变化的驱动。<br>相比于SpringBoot 的  @Scheduled 调度强大的多。</p>
<p>（真实环境下，也许是列车驾驶员人工去发送信息）。</p>
<h3 id="ER图"><a href="#ER图" class="headerlink" title="ER图"></a>ER图</h3><h3 id="业务量究竟大在哪里"><a href="#业务量究竟大在哪里" class="headerlink" title="业务量究竟大在哪里"></a>业务量究竟大在哪里</h3><p>电商最头疼的是什么模块？</p>
<p>订单？支付？都不是，是库存！！！</p>
<p>为什么？因为库存是<strong>多读多写</strong>的场景，是整个性能的瓶颈！</p>
<p>订单，就用户一个人可以写，你爱怎么改怎么改，分布式也好做，因为只有单写。</p>
<p>库存不一样，同一个sku每个订单都要改库存，这太恐怖了，一般电商还可以通过分仓库，预分配等手段解决，火车票你怎么搞！！分仓库？哦，今天北京发往上海的G123车钱，华北地区还有三张票，西南地区库存为0？不可能！！！全国人民在极短的时间内，大量订单抢同一个库存，这本来就已经非常困难了，再加上，几乎所有热门车票都特么这么抢！而且，一列火车，由于乘客的起始站和终点站不同，可以逻辑上分成多种商品，但这些商品又相互有库存关联，无法划为独立sku…想想就头疼，再加上在多写的库上做分布式，同步问题又是老大难。所以，12306非常困难！！</p>
<h3 id="库存是什么-——-火车售票的余票算法"><a href="#库存是什么-——-火车售票的余票算法" class="headerlink" title="库存是什么? —— 火车售票的余票算法"></a>库存是什么? —— 火车售票的余票算法</h3><h4 id="路径节点对构成-sku"><a href="#路径节点对构成-sku" class="headerlink" title="路径节点对构成 sku"></a>路径节点对构成 sku</h4><p>网上自称某宝工程师写的12306文章。他们将<strong>路径上的每个站点对</strong>独立成一件商品，每次购、退票都需要查询删改库存，造成巨大的数据库操作开销。</p>
<p>将每站点全部商品位化，是为了<strong>营造高并发的假象</strong>而已，白白浪费了计算资源。以每趟车2000张票为例子。</p>
<p>某宝的工程师算法逻辑是直接操作库的逻辑，那么将数据库映射成为bmp处理，但这又带来新的问题，比如锁定机制，数据同步机制，写入仲裁机制，这些在本算法中由天然的cpu硬件机制来实施。与硬件有一致性，机制成熟算法健壮性有保证。如果人为的另立机制想拓展bmp算法的性能会导致很多问题，</p>
<p>![image-20220316031959849](..&#x2F;..&#x2F;..&#x2F;..&#x2F;..&#x2F;Library&#x2F;Application Support&#x2F;typora-user-images&#x2F;image-20220316031959849.png)</p>
<h4 id="站点座位bitmap法"><a href="#站点座位bitmap法" class="headerlink" title="站点座位bitmap法"></a>站点座位bitmap法</h4><p>抢票的基本单位是某一列列车。</p>
<p>对<strong>此列列车经过的所有站点都创建等容量大小的位图（容量大小为座位数量）</strong>—— 当然，如果有多个类型的座位，同理每种类型都创建位图即可。</p>
<p><strong>位图0表示空闲，1表示已占。</strong></p>
<p><strong>用户每次买票获取此列车对应所有中途站点的位图，对这些位图（或者说二进制串）做按位或运算</strong>，</p>
<p>运算结果为1的表明不能被购买（只要有一个为1，就不能购买），为0的表明还没有人购买，可以售出。</p>
<p>如果有用户抢到某个余座，需要将这几个连续的位图的对应bit的0全部改成1（原子地更新）。</p>
<p><a target="_blank" rel="noopener" href="https://mp.weixin.qq.com/s?__biz=MzkxNDEyOTI0OQ==&mid=2247484331&idx=1&sn=3e0294efe74cd7b9a421aef778081c69&source=41#wechat_redirect">https://mp.weixin.qq.com/s?__biz=MzkxNDEyOTI0OQ==&amp;mid=2247484331&amp;idx=1&amp;sn=3e0294efe74cd7b9a421aef778081c69&amp;source=41#wechat_redirect</a></p>
<h2 id="”库存“扣减的问题"><a href="#”库存“扣减的问题" class="headerlink" title="”库存“扣减的问题"></a>”库存“扣减的问题</h2><h3 id="超卖问题的解决"><a href="#超卖问题的解决" class="headerlink" title="超卖问题的解决"></a>超卖问题的解决</h3><h4 id="悲观锁"><a href="#悲观锁" class="headerlink" title="悲观锁"></a>悲观锁</h4><p>悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。</p>
<p>悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。</p>
<p>Java synchronized、ReentrantLock 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> ... LOCK <span class="keyword">IN</span> SHARE MODE;  <span class="operator">/</span><span class="operator">/</span> <span class="keyword">SELECT</span> ... <span class="keyword">FOR</span> SHARE;</span><br><span class="line"><span class="keyword">SELECT</span> ... <span class="keyword">FOR</span> <span class="keyword">UPDATE</span>;</span><br></pre></td></tr></table></figure>



<p>具体一点的一点：</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">&quot;selectWithXLock&quot;</span> <span class="attr">resultMap</span>=<span class="string">&quot;TrainStationSeatStatusMap&quot;</span>&gt;</span></span><br><span class="line">    select</span><br><span class="line">    train_id,</span><br><span class="line">    working_date,</span><br><span class="line">    type,</span><br><span class="line">    station_id,</span><br><span class="line">    status</span><br><span class="line">    from train_station_seat_status</span><br><span class="line">    where</span><br><span class="line">    train_id = #&#123;trainId&#125;             // 列车id</span><br><span class="line">    and working_date = #&#123;workingDate&#125; // 日期</span><br><span class="line">    and type = #&#123;type&#125;                // 座位类型</span><br><span class="line">    and station_id in                 // 车站id</span><br><span class="line">    <span class="tag">&lt;<span class="name">foreach</span> <span class="attr">item</span>=<span class="string">&quot;item&quot;</span> <span class="attr">index</span>=<span class="string">&quot;index&quot;</span> <span class="attr">collection</span>=<span class="string">&quot;stationIds&quot;</span></span></span><br><span class="line"><span class="tag">             <span class="attr">open</span>=<span class="string">&quot;(&quot;</span> <span class="attr">separator</span>=<span class="string">&quot;,&quot;</span> <span class="attr">close</span>=<span class="string">&quot;)&quot;</span>&gt;</span></span><br><span class="line">        #&#123;item&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">foreach</span>&gt;</span></span><br><span class="line">    for update ;                      // 写锁</span><br><span class="line"><span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br></pre></td></tr></table></figure>



<p><strong>悲观锁</strong></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 悲观锁</span></span><br><span class="line"><span class="meta">@Override</span></span><br><span class="line"><span class="keyword">public</span> SeatLocation <span class="title function_">buyTicketAndOccupySeatWithPessimisticLock</span><span class="params">(DrivingPlan drivingPlan, SeatType seatType)</span> <span class="keyword">throws</span> TicketSaleCoreException &#123;</span><br><span class="line">    <span class="type">Integer</span> <span class="variable">trainId</span> <span class="operator">=</span> drivingPlan.getTrain().getTrainId();</span><br><span class="line">    <span class="type">LocalDate</span> <span class="variable">workingDate</span> <span class="operator">=</span> drivingPlan.getWorkingDate();</span><br><span class="line">    ArrayList&lt;Integer&gt; stationIds = <span class="keyword">new</span> <span class="title class_">ArrayList</span>&lt;&gt;();</span><br><span class="line">    drivingPlan.getSeatStatusOfPassStations(seatType).forEach(trainStationSeatStatus -&gt; stationIds.add(trainStationSeatStatus.getStationId()));</span><br><span class="line">    <span class="comment">// 加互斥锁，一次性拿到所有需要的状态位图</span></span><br><span class="line">    List&lt;TrainStationSeatStatus&gt; trainStationSeatStatuses = trainStationSeatStatusMapper.selectWithXLock(trainId, workingDate, seatType, stationIds);</span><br><span class="line">    <span class="comment">// 检查位图结构</span></span><br><span class="line">    checkHasSameCapacity(trainStationSeatStatuses);</span><br><span class="line">    <span class="comment">// 二进制串集作按位或运算</span></span><br><span class="line">    <span class="type">boolean</span>[] availSeats = getAvailSeats(trainStationSeatStatuses);</span><br><span class="line">    <span class="comment">// 获取所有可用的座位</span></span><br><span class="line">    List&lt;Integer&gt; availSeatsIds = getAvailSeats(availSeats);</span><br><span class="line">    <span class="keyword">if</span> (availSeatsIds.isEmpty()) &#123;</span><br><span class="line">        <span class="keyword">return</span> <span class="literal">null</span>;</span><br><span class="line">    &#125;</span><br><span class="line">    <span class="comment">// 随机从空闲座位中挑一个空闲座位</span></span><br><span class="line">    <span class="type">int</span> <span class="variable">idx</span> <span class="operator">=</span> randomSeatIndex(availSeats);</span><br><span class="line">    <span class="comment">// 更改位图</span></span><br><span class="line">    occupyOneSeat(idx, trainStationSeatStatuses);</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 更改座位-位图状态</span></span><br><span class="line">    trainStationSeatStatuses.forEach(trainStationSeatStatus -&gt; &#123;</span><br><span class="line">        <span class="type">Integer</span> <span class="variable">stationId</span> <span class="operator">=</span> trainStationSeatStatus.getStationId();</span><br><span class="line">        trainStationSeatStatusMapper.update(</span><br><span class="line">                trainStationSeatStatus,</span><br><span class="line">                <span class="keyword">new</span> <span class="title class_">UpdateWrapper</span>&lt;TrainStationSeatStatus&gt;()</span><br><span class="line">                        .eq(<span class="string">&quot;train_id&quot;</span>, trainId)</span><br><span class="line">                        .eq(<span class="string">&quot;working_date&quot;</span>, workingDate)</span><br><span class="line">                        .eq(<span class="string">&quot;type&quot;</span>, seatType)</span><br><span class="line">                        .eq(<span class="string">&quot;station_id&quot;</span>, stationId)</span><br><span class="line">        );</span><br><span class="line"></span><br><span class="line">    &#125;);</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 查询对应位置的座位</span></span><br><span class="line">    <span class="keyword">return</span> seatMapper.selectOne(<span class="keyword">new</span> <span class="title class_">QueryWrapper</span>&lt;TrainPatternSeatMap&gt;()</span><br><span class="line">            .eq(<span class="string">&quot;train_pattern_id&quot;</span>, drivingPlan.getTrain().getTrainPatternId())</span><br><span class="line">            .eq(<span class="string">&quot;type&quot;</span>, seatType)</span><br><span class="line">            .eq(<span class="string">&quot;idx&quot;</span>, idx)).getSeatLocation();</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>



<h4 id="乐观锁"><a href="#乐观锁" class="headerlink" title="乐观锁"></a>乐观锁</h4><p>乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。</p>
<p>乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。</p>
<p>使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> ... <span class="keyword">SET</span> ... <span class="operator">=</span> ... <span class="keyword">WHERE</span> version <span class="operator">=</span> #&#123;version&#125;</span><br></pre></td></tr></table></figure>



<p>但是在本例中，不能简单地使用这个方案，因为这里需要原子性地一次性修改掉多个位串记录。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 乐观锁</span></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> SeatLocation <span class="title function_">buyTicketAndOccupySeatWithOptimisticLock</span><span class="params">(DrivingPlan drivingPlan, SeatType seatType)</span> <span class="keyword">throws</span> TicketSaleCoreException, OptimisticLockFailure &#123;</span><br><span class="line">        <span class="type">Integer</span> <span class="variable">trainId</span> <span class="operator">=</span> drivingPlan.getTrain().getTrainId();</span><br><span class="line">        <span class="type">LocalDate</span> <span class="variable">workingDate</span> <span class="operator">=</span> drivingPlan.getWorkingDate();</span><br><span class="line"><span class="comment">//         先乐观锁尝试</span></span><br><span class="line"><span class="comment">//         无锁读</span></span><br><span class="line">        List&lt;TrainStationSeatStatus&gt; seatStatusOfPassStations = drivingPlan.getSeatStatusOfPassStations(seatType);</span><br><span class="line">        List&lt;TrainStationSeatStatus&gt; realStationSeatStatuses = <span class="keyword">new</span> <span class="title class_">ArrayList</span>&lt;&gt;();</span><br><span class="line">        seatStatusOfPassStations.forEach(trainStationSeatStatus -&gt; &#123;</span><br><span class="line">            <span class="type">TrainStationSeatStatus</span> <span class="variable">status</span> <span class="operator">=</span> trainStationSeatStatusMapper.selectOne(<span class="keyword">new</span> <span class="title class_">QueryWrapper</span>&lt;TrainStationSeatStatus&gt;()</span><br><span class="line">                    .eq(<span class="string">&quot;train_id&quot;</span>, trainId)</span><br><span class="line">                    .eq(<span class="string">&quot;working_date&quot;</span>, workingDate)</span><br><span class="line">                    .eq(<span class="string">&quot;type&quot;</span>, seatType)</span><br><span class="line">                    .eq(<span class="string">&quot;station_id&quot;</span>, trainStationSeatStatus.getStationId()));</span><br><span class="line">            realStationSeatStatuses.add(status);</span><br><span class="line">        &#125;);</span><br><span class="line">        seatStatusOfPassStations = realStationSeatStatuses;</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 根据二进制位图检查座位的可用情况</span></span><br><span class="line">        checkHasSameCapacity(seatStatusOfPassStations);</span><br><span class="line">        <span class="type">boolean</span>[] availSeats = getAvailSeats(seatStatusOfPassStations);</span><br><span class="line">        List&lt;Integer&gt; availSeatsIds = getAvailSeats(availSeats);</span><br><span class="line">        <span class="comment">// 快照读，若此时无票，则直接返回</span></span><br><span class="line">        <span class="keyword">if</span> (availSeatsIds.isEmpty()) &#123;</span><br><span class="line">            <span class="keyword">return</span> <span class="literal">null</span>;</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 尝试占座</span></span><br><span class="line">        <span class="type">int</span> <span class="variable">idx</span> <span class="operator">=</span> randomSeatIndex(availSeats);</span><br><span class="line">        occupyOneSeat(idx, seatStatusOfPassStations);</span><br><span class="line">        <span class="keyword">for</span> (TrainStationSeatStatus trainStationSeatStatus : seatStatusOfPassStations) &#123;</span><br><span class="line">            <span class="type">int</span> <span class="variable">succ</span> <span class="operator">=</span> trainStationSeatStatusMapper.update(trainStationSeatStatus,</span><br><span class="line">                    <span class="keyword">new</span> <span class="title class_">UpdateWrapper</span>&lt;TrainStationSeatStatus&gt;()</span><br><span class="line">                            .eq(<span class="string">&quot;train_id&quot;</span>, trainId)</span><br><span class="line">                            .eq(<span class="string">&quot;working_date&quot;</span>, workingDate)</span><br><span class="line">                            .eq(<span class="string">&quot;type&quot;</span>, trainStationSeatStatus.getType())</span><br><span class="line">                            .eq(<span class="string">&quot;station_id&quot;</span>, trainStationSeatStatus.getStationId()));</span><br><span class="line">            <span class="comment">// 只要有一个更新失败，前面的操作必须全部回滚，这里的回滚绝不可以是自己做&quot;补偿操作&quot;，而应该是抛出异常迫使回滚</span></span><br><span class="line">            <span class="keyword">if</span> (succ == <span class="number">0</span>) &#123;</span><br><span class="line">                <span class="keyword">throw</span> <span class="keyword">new</span> <span class="title class_">OptimisticLockFailure</span>();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">// 流程到这里，表明乐观锁争抢修改成功</span></span><br><span class="line">        <span class="comment">// 查询对应位置的座位</span></span><br><span class="line">        <span class="keyword">return</span> seatMapper.selectOne(<span class="keyword">new</span> <span class="title class_">QueryWrapper</span>&lt;TrainPatternSeatMap&gt;()</span><br><span class="line">                .eq(<span class="string">&quot;train_pattern_id&quot;</span>, drivingPlan.getTrain().getTrainPatternId())</span><br><span class="line">                .eq(<span class="string">&quot;type&quot;</span>, seatType)</span><br><span class="line">                .eq(<span class="string">&quot;idx&quot;</span>, idx)).getSeatLocation();</span><br><span class="line">    &#125;</span><br></pre></td></tr></table></figure>







<h4 id="自适应锁"><a href="#自适应锁" class="headerlink" title="自适应锁"></a>自适应锁</h4><p>不要有一种错觉，觉得乐观锁实现了无锁并发安全，就觉得乐观锁的性能远远优越悲观锁，</p>
<p>这个是有几个前提的，读多写少，竞争程度不激烈，短事务。（总而言之，减少因为乐观锁加锁失败重试的次数）</p>
<p>所以，我的策略是，对于上述两种加锁方式，同时予以实现。<br>具体的，默认先使用乐观锁并发修改数据，同时设定乐观锁失败重试次数做多为1次（可以根据实际情况修改配置），如果仍然未修改成功，再改用悲观锁进行竞争修改数据。</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> SeatLocation <span class="title function_">buyTicketAndOccupySeat</span><span class="params">(DrivingPlan drivingPlan, SeatType seatType)</span> <span class="keyword">throws</span> TicketSaleCoreException &#123;</span><br><span class="line"><span class="comment">//        System.out.println(&quot;乐观锁最大重试次数 &quot; + MAX_OPTIMISTIC_LOCK_RETRIES);</span></span><br><span class="line">        <span class="keyword">for</span> (<span class="type">int</span> <span class="variable">i</span> <span class="operator">=</span> <span class="number">0</span>; i &lt; MAX_OPTIMISTIC_LOCK_RETRIES; i++) &#123;</span><br><span class="line">            <span class="type">SeatLocation</span> <span class="variable">seatLocation</span> <span class="operator">=</span> transactionTemplate.execute(transactionStatus -&gt; &#123;</span><br><span class="line">                <span class="keyword">try</span> &#123;</span><br><span class="line">                    <span class="comment">// 没有抛出自定义的异常，说明一切正常，</span></span><br><span class="line">                    <span class="keyword">return</span> buyTicketAndOccupySeatWithOptimisticLock(drivingPlan, seatType);</span><br><span class="line">                &#125; <span class="keyword">catch</span> (OptimisticLockFailure | TicketSaleCoreException failure) &#123;</span><br><span class="line">                    transactionStatus.setRollbackOnly();</span><br><span class="line">                    <span class="keyword">return</span> <span class="literal">null</span>;</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;);</span><br><span class="line">            <span class="comment">// 乐观锁已经争抢成功</span></span><br><span class="line">            <span class="keyword">if</span> (seatLocation != <span class="literal">null</span>) <span class="keyword">return</span> seatLocation;</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">return</span> buyTicketAndOccupySeatWithPessimisticLock(drivingPlan, seatType);</span><br><span class="line">    &#125;</span><br></pre></td></tr></table></figure>



<h2 id="技术难点举例"><a href="#技术难点举例" class="headerlink" title="技术难点举例"></a>技术难点举例</h2><h3 id="库存超卖"><a href="#库存超卖" class="headerlink" title="库存超卖"></a>库存超卖</h3><p>这里的库存指的就是一般意义上的库存：</p>
<blockquote>
<p>常见的库存扣减方式有：</p>
<ul>
<li>下单减库存：即当买家下单后，在商品的总库存中减去买家购买数量。下单减库存是最简单的减库存方式，也是控制最精确的一种，下单时直接通过数据库的事务机制控制商品库存，这样一定不会出现超卖的情况。但是你要知道，有些人下完单可能并不会付款。</li>
<li>付款减库存：即买家下单后，并不立即减库存，而是等到有用户付款后才真正减库存，否则库存一直保留给其他买家。但因为付款时才减库存，如果并发比较高，有可能出现买家下单后付不了款的情况，因为可能商品已经被其他人买走了。</li>
<li>预扣库存：这种方式相对复杂一些，买家下单后，库存为其保留一定的时间（如 30 分钟），超过这个时间，库存将会自动释放，释放后其他买家就可以继续购买。在买家付款前，系统会校验该订单的库存是否还有保留：如果没有保留，则再次尝试预扣；如果库存不足（也就是预扣失败）则不允许继续付款；如果预扣成功，则完成付款并实际地减去库存。<br> 至于采用哪一种减库存方式更多是业务层面的考虑，减库存最核心的是大并发请求时保证数据库中的库存字段值不能为负数。</li>
</ul>
</blockquote>
<p>解决方案已经在上面已经有了部分描述——介绍了纯数据库层面的解决方案。</p>
<p>下面介绍一下使用Redis的做法：<strong>Lua脚本处理秒杀请求</strong></p>
<p>大致处理过程如下：</p>
<ol>
<li>将商品信息预热到redis中；</li>
<li>用户扣库存请求用lua在redis里完成</li>
<li>及时将redis的记录落到数据库（定时任务）</li>
</ol>
<h3 id="避免重复下单"><a href="#避免重复下单" class="headerlink" title="避免重复下单"></a>避免重复下单</h3><p>用户快速点了两次 “提交订单”  按钮，浏览器会向后端发送两条创建订单的请求，最终会创建两条一模一样的订单。</p>
<p><strong>幂等性如何保证</strong>？</p>
<ol>
<li><strong>先select后insert</strong></li>
</ol>
<p>这种插入数据（不论是新增一行，还是将修改字段的空值）的场景比较容易处理，先查后改。</p>
<ol start="2">
<li><strong>数据库自身特性 “主键唯一约束”</strong></li>
</ol>
<p>利用数据库自身特性 “主键唯一约束”，在插入订单记录时，带上主键值，如果订单重复，记录插入会失败。</p>
<p>操作过程：</p>
<p>引入一个服务，用于生成一个“全局唯一的订单号”；</p>
<p>进入创建订单页面时，前端请求该服务，预生成订单ID；</p>
<p>提交订单时，请求参数除了业务参数外，还要带上这个预生成订单ID。</p>
<ol start="3">
<li><strong>建防重表</strong></li>
</ol>
<p>有时候表中并非所有的场景都不允许产生重复的数据，只有某些特定场景才不允许。这时候，直接在表中加唯一索引，显然是不太合适的。</p>
<p>针对这种情况，我们可以通过<code>建防重表</code>来解决问题。</p>
<p>该表可以只包含两个字段：<code>id</code> 和 <code>唯一索引</code>，唯一索引可以是多个字段比如：name、code等组合起来的唯一标识，例如：susan_0001。</p>
<ol start="4">
<li><strong>建立业务状态机</strong></li>
</ol>
<p>很多时候业务表是有状态的，比如订单表中有：1-下单、2-已支付、3-完成、4-撤销等状态。如果这些状态的值是有规律的，按照业务节点正好是从小到大，我们就能通过它来保证接口的幂等性。</p>
<ol start="5">
<li><strong>分布式锁</strong></li>
</ol>
<p>具体使用分布式锁，各式各样了。不过核心思想就是<code>setnx</code>。</p>
<ol start="6">
<li><strong>带过期期限的token</strong></li>
</ol>
<p>请求时先请求token，token存redis，带过期时间。实际业务处理时redis里有token直接返回，反之执行数据操作。</p>
<h3 id="订单分库分表，多维度查询"><a href="#订单分库分表，多维度查询" class="headerlink" title="订单分库分表，多维度查询"></a>订单分库分表，多维度查询</h3><p>如果电商网站的订单数过多，我们一般会想到 <code>分库分表</code> 解决策略。没问题，这个大方向是对的。</p>
<p><strong>但是查询维度很多。</strong></p>
<p>1、买家，查询 <code>我的订单</code> 列表，需要根据 <code>buyer_id</code> 来查询</p>
<p>2、查看订单详情，需要根据 <code>order_id</code> 来查询</p>
<p>3、卖家，查询 <code>我的销售</code> 列表，需要根据 <code>seller_id</code> 来查询</p>
<p><strong>而订单分表只有一个分表键，如何满足多维度 SQL 操作呢</strong>？</p>
<p>如何调节分片键与索引的矛盾: <strong>冗余</strong>。</p>
<ol>
<li>全量冗余</li>
<li>关系冗余</li>
</ol>
<p>举例:<br>order表</p>
<p>![order_table](..&#x2F;..&#x2F;..&#x2F;..&#x2F;..&#x2F;Library&#x2F;Application Support&#x2F;typora-user-images&#x2F;image-20220316031324461.png)</p>
<h3 id="分布式事务"><a href="#分布式事务" class="headerlink" title="分布式事务"></a>分布式事务</h3><h4 id="XA"><a href="#XA" class="headerlink" title="XA"></a>XA</h4><h4 id="Seata-的-AT"><a href="#Seata-的-AT" class="headerlink" title="Seata 的 AT"></a>Seata 的 AT</h4>
    </div>

    
    
    

    <footer class="post-footer">

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2021/08/23/HikariCP%E9%85%8D%E7%BD%AE%E4%BF%A1%E6%81%AF/" rel="prev" title="HikariCP配置信息">
                  <i class="fa fa-chevron-left"></i> HikariCP配置信息
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2021/09/14/Redis%E7%B3%BB%E5%88%97%E7%AC%94%E8%AE%B0%E4%B9%8B%20%E2%80%94%E2%80%94%20%E7%AE%80%E4%BB%8B%E3%80%81%E5%AE%89%E8%A3%85%E3%80%81%E5%85%A5%E9%97%A8%E3%80%81%E9%85%8D%E7%BD%AE/" rel="next" title="Redis系列笔记之 —— 简介、安装、入门、配置">
                  Redis系列笔记之 —— 简介、安装、入门、配置 <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">SongyangJi</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/muse/" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>
  <div class="sidebar-dimmer"></div>
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script src="https://cdnjs.cloudflare.com/ajax/libs/animejs/3.2.1/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/schemes/muse.js"></script><script src="/js/next-boot.js"></script>

  <script src="https://cdnjs.cloudflare.com/ajax/libs/hexo-generator-searchdb/1.4.1/search.js" integrity="sha256-1kfA5uHPf65M5cphT2dvymhkuyHPQp5A53EGZOnOLmc=" crossorigin="anonymous"></script>
<script src="/js/third-party/search/local-search.js"></script>





  





</body>
</html>
